import math
import os
import time
import sys
import openpyxl

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.CcxDataCenterDb import CcxDataCenter
from src.utils import CommonUtils, BatchUtils
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


def main():
    logger = Logger()
    cleanScreen()
    t = time.perf_counter()

    logger.info("========中诚信行业整理导入 开始 ========")
    workbook = openpyxl.load_workbook("d://中诚信行业整理0428.xlsx")

    # 获取当前活动工作表（默认为第一张）
    sheet = workbook.active
    # 遍历每一行并输出 A 列的值
    i = 0
    dataInfo = []
    updateDatas = []
    unUsedataInfo = []
    for row in sheet.iter_rows(min_row=1, min_col=1, max_row=sheet.max_row, max_col=5, values_only=True):
        if i > 0:
            for cell in row:
                if cell is not None :
                    print(cell)
                    dataInfo.append(cell)
                else:
                    dataInfo.append("-")
        i = i + 1
    datalen = dataInfo.__len__()
    for num in range(0,datalen-1,5):
        print(num)
        if dataInfo[num+4] != "-":
            third_code = queryCode(dataInfo[num+4],2)
            if third_code[0]['code'] != "-":
                second_code = queryParentCode(third_code[0]['code'])
                second_name = queryNameByCode(second_code[0]['parent_code'],1)
                first_code = queryParentCode(second_code[0]['parent_code'])
                first_name = queryNameByCode(first_code[0]['parent_code'],0)
                updateDatas.append((
                    dataInfo[num + 1],
                    dataInfo[num],
                    first_code[0]['parent_code'],
                    first_name[0]['name'],
                    second_code[0]['parent_code'],
                    second_name[0]['name'],
                    third_code[0]['code'],
                    dataInfo[num + 4]
                ))
            else:
                unUsedataInfo.append(dataInfo[num])
        else:
            unUsedataInfo.append(dataInfo[num])
    BatchUtils.batchInsert(codeUpdates ,updateDatas)



def codeUpdates(updateDatas):
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    sql = """
    REPLACE INTO `ccx_ent_industry_code` (`code`, `name`, `first_code`,`first_name`, `second_code`,`second_name`,`third_code`,`third_name`, `deltet_flag`, `update_user`) VALUES 
    (%s, %s, %s, %s , %s, %s, %s , %s, 0, 'system');
    """
    return dataCenterDb.ExecMany(sql, updateDatas)

def queryCode(queryCode, level):
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    sql = """
        select code from ccx_industry_code where name = %s and level= %s
    """
    return dataCenterDb.Query(sql, (queryCode, level))

def queryParentCode(queryCode):
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    sql = """
        select parent_code from ccx_industry_code where code = %s
    """
    return dataCenterDb.Query(sql, queryCode)

def queryNameByCode(queryCode, level):
    dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
    sql = """
        select name from ccx_industry_code where code = %s and level= %s
    """
    return dataCenterDb.Query(sql, (queryCode, level))

# 程序入口
if __name__ == "__main__":
    main()
